package org.ssp.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class MySqlUtil {
	//连接数据库
	public  Connection getConnection() throws SQLException{
		String url="jdbc:mysql://localhost/nutzbook";
		String user="root";
		String password="";
		Connection conn=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("驱动加载成功");
			conn=DriverManager.getConnection(url,user,password);
			System.out.println("数据库连接成功");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return conn;
		
	}
	
	//释放资源
	public void releaseResource(Connection conn,PreparedStatement pstm,ResultSet rs){
			
		try {
			if(rs!=null)
				rs.close();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		try {
			if(pstm!=null)				
				pstm.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {			
			if(conn!=null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
			
	}
	//释放资源
	public void releaseResource(Connection conn,Statement stm,ResultSet rs){
			
		try {
			if(rs!=null)
				rs.close();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		
		try {
			if(stm!=null)				
				stm.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {			
			if(conn!=null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
			
	}
	/**
	 * 创建数据库表格
	 * @param conn 当前数据库连接对象
	 * @param sql  数据库语句
	 */
	public static void createTable(String sql){
		MySqlUtil mysql=new MySqlUtil();
		Connection conn = null;
	    Statement stm=null;

		try {
			conn = mysql.getConnection();
			stm=conn.createStatement();
	    	stm.execute(sql);  	
			
		} catch (SQLException e1) {
			e1.printStackTrace();
		}finally{
			mysql.releaseResource(conn,stm, null);
			
		}

	}

	/**
	 * 签到时，保存用户信息
	 * 
	 * @param openid
	 */
	public static void saveWeinxinUser(String openid){
		MySqlUtil mysql=new MySqlUtil();
		Connection conn=null;
		String sql="insert into weixin_user(open_id,subscribe_time,subscribe_status,points) values(?,now(),?,?)";
		PreparedStatement pstm=null;
		try {
			conn = mysql.getConnection();
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, openid);
			pstm.setLong(2, 1);
			pstm.setLong(3, 0);
			pstm.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			mysql.releaseResource(conn,pstm,null);
		}
		
	}
	
	/**
	 * 保存签到信息
	 * @param openid
	 * @param signPoints
	 */
	public static void saveWeixinSign(String openid,int signPoints){
		MySqlUtil mysql=new MySqlUtil();
		Connection conn=null;
		String sql="insert into weixin_sign(open_id,sign_time,sign_points) values(?,now(),?)";
		PreparedStatement pstm=null;
		try {
			conn = mysql.getConnection();
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, openid);
			pstm.setLong(2, signPoints);
			pstm.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			mysql.releaseResource(conn,pstm,null);
		}
		
	}
	
	/**
	 * 更新用户总积分
	 * @param openid
	 * @param i
	 */
	public static void updateUserPoints(String openid,int i){
		MySqlUtil mysql=new MySqlUtil();
		Connection conn=null;
		String sql="update weixin_user set points=points+? where open_id=?";
		PreparedStatement pstm=null;
		try {
			conn = mysql.getConnection();
			pstm=conn.prepareStatement(sql);
			pstm.setLong(1, i);
			pstm.setString(2, openid);
			pstm.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			mysql.releaseResource(conn,pstm,null);
		}
	}
	
	/**
	 * 判断用户今天是否签到过
	 * @param openid
	 * @return
	 */
	public static boolean isTodaySigned(String openid){
		boolean result=false;
		MySqlUtil mysql=new MySqlUtil();
		Connection conn=null;
		String sql="select count(*) as signCounts from weixin_sign where open_id=? and DATE_FORMAT(sign_time,'%Y-%m-%d')=DATE_FORMAT(now(),'%Y-%m-%d')";
		ResultSet rs=null;
		PreparedStatement pstm=null;
		try {
			conn = mysql.getConnection();
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, openid);
			//返回查询结果的对象
			rs=pstm.executeQuery();
			int signCounts=0;
			if(rs.next()){
				signCounts=rs.getInt("signCounts");
				
			}
			if(signCounts==1){
				result=true;
			}
						
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			mysql.releaseResource(conn,pstm,rs);
		}
		return result;
		
	}	
	/**
	 * 
	 * 判断是不是连续第七天签到
	 * 
	 * @param openid 用户的openid
	 * @param monday 本周周一的日期(YYYY-mm-dd HH:mm:ss)
	 * @return
	 */
	
	public static boolean isSevenSign(String openid,String monday){
		boolean result=false;
		MySqlUtil mysql=new MySqlUtil();
		Connection conn=null;
		String sql="select count(*) as signCounts from weixin_sign where open_id=? and sign_time between str_to_date('?','%Y-%m-%d %H:%i:%s') and now()";
		ResultSet rs=null;
		PreparedStatement pstm=null;
		try {
			conn = mysql.getConnection();
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, openid);
			pstm.setString(1, monday);
			rs=pstm.executeQuery();
			int signCounts=0;
			if(rs.next()){
				signCounts=rs.getInt("signCounts");
			}
			if(signCounts==6){
				result=true;
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			mysql.releaseResource(conn,pstm,rs);
		}
		return result;
		
	}
	
	/**
	 * 
	 * 获取本周一对应的日期
	 * 
	 * @return
	 */
	public static String getMondayOfThisWeek(){
		DateFormat df=new SimpleDateFormat("yyyy-MM-dd");
		Calendar c=Calendar.getInstance();
		//得到今天是星期几
		int dayOfWeek=c.get(Calendar.DAY_OF_WEEK)-1;
		//对星期天的特殊处理
		if(dayOfWeek==0){
			dayOfWeek=7;
		}
		//假如今天是星期四，往前推三天是星期一，即(4-1)
		c.add(Calendar.DATE, -(dayOfWeek-1));
		return df.format(c.getTime());		
	}
	
	/**
	 * 取消关注时，删除weixin_user和weixin_sign表的记录
	 * 
	 * 
	 */
//	public static void deleteRecords(){
//		MySqlUtil mysql=new MySqlUtil();
//		Connection conn=null;
//		String sql1="drop table weixin_user";
//		String sql2="drop table weixin_sign";
//		PreparedStatement pstm1=null;
//		PreparedStatement pstm2=null;
//		try {
//			conn = mysql.getConnection();
//			pstm1=conn.prepareStatement(sql1);
//			pstm2=conn.prepareStatement(sql2);
//		
//			pstm1.execute();
//			pstm2.execute();
//			
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}finally{
//			mysql.releaseResource(conn,pstm1,null);
//			mysql.releaseResource(conn,pstm2,null);
//
//		}
//	}

}
